package com.googlecode.bip.dao.mysql;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import javax.naming.NamingException;
import org.apache.log4j.Logger;
import com.googlecode.bip.dao.UserDAO;
import com.googlecode.bip.dao.utils.DBHelper;
import com.googlecode.bip.entity.IUser;
import com.googlecode.bip.entity.User;
import cx.ath.choisnet.sql.ConnectionQuery;

/**
 *
 * @author Claude CHOISNET
 */
public class MySQLUserDAO extends UserDAO
{
    private static final long serialVersionUID = 1L;
    private static final Logger logger = Logger.getLogger( MySQLUserDAO.class );

    @Override //Listable<IUser>
    public List<IUser> getList( String sqlQuery ) throws SQLException, NamingException
    {
        Connection connection = MySQLDAOFactory.getConnection();

        try {
            return DBHelper.getList( this, connection, sqlQuery );
            }
        finally {
            MySQLDAOFactory.returnConnectionToPool( connection );
            }
    }

    @Override
    public IUser findUser( final String username, final String password )
        throws NamingException, SQLException
    {
        final String sql = "SELECT * FROM `users` WHERE `username`='"
                + username
                + "' AND `password`='"
                + password
                + "'";

        if( logger.isDebugEnabled() ) {
            logger.debug( "findUser = " + sql );
            }

        Connection connection = MySQLDAOFactory.getConnection();
        final ConnectionQuery query = new ConnectionQuery( connection );

        try {
            ResultSet   rSet  = query.executeQuery( sql );
            boolean     found = rSet.first();

            if( found ) {
                return new User().set( rSet );
                }
            }
        finally {
            query.quietClose();
            MySQLDAOFactory.returnConnectionToPool( connection );
            }

        return null;
    }

    @Override
    public IUser findUser( int iduser ) throws NamingException, SQLException
    {
        final String sql = "SELECT * FROM `users` WHERE `iduser`='"
            + iduser
            + "'";

        if( logger.isDebugEnabled() ) {
            logger.debug( "findUser = " + sql );
            }

        Connection connection = MySQLDAOFactory.getConnection();
        final ConnectionQuery query = new ConnectionQuery( connection );

        try {
            ResultSet   rSet  = query.executeQuery( sql );
            boolean     found = rSet.first();

            if( found ) {
                return new User().set( rSet );
                }
            }
        finally {
            query.quietClose();
            MySQLDAOFactory.returnConnectionToPool( connection );
            }

        return null;
    }

    /**
     * Insert a new User
     * @param user User to insert
     * @return true if User has been created
     */
    @Override
    public boolean insertUser( final IUser iuser )
    {
        User user = User.class.cast( iuser );

        try {
            final String sql = "CALL add_user('"
                + user.getUserName()
                + "','"
                + user.getPassword()
                + "')"
                ;

            int iduser = private_connectUser( sql, user.getUserName() );

            // Update iduser
            user.protected_setIdUser( iduser );

            return updateUser( user );
            }
        catch( Exception e ) {
            logger.error( "*** createUser: ", e );

            return false;
            }
    }

    /**
     * Delete a User
     * @param user User to delete
     * @return true if User has been deleted, false on failure
     * @throws NamingException
     * @throws SQLException
     */
    @Override
    public boolean deleteUser( final int iduser )
        throws NamingException, SQLException
    {
        final String sql = "DELETE FROM `users` WHERE `iduser`='" + iduser + "';";

        if( logger.isDebugEnabled() ) {
            logger.debug( "deleteUser = " + sql );
            }

        Connection connection = MySQLDAOFactory.getConnection();
        final ConnectionQuery query = new ConnectionQuery( connection );

        try {
            int x = query.doUpdate( sql );

            if( x == 1 ) {
                return true;
                }
            }
        finally {
            query.quietClose();
            MySQLDAOFactory.returnConnectionToPool( connection );
            }

        return false;
    }

    /**
     * Update User in database
     *
     * @param user user to update
     * @return true on success, false on failure or error
     * @throws NamingException
     * @throws SQLException
     */
    @Override
    public boolean updateUser( final IUser iuser )
        throws NamingException, SQLException
    {
        User user = User.class.cast( iuser );

        final StringBuilder sql = new StringBuilder();

        sql.append( "UPDATE `users` AS U SET U.`username`='" );
        sql.append( user.getUserName() );
        sql.append( "', " );
        sql.append( "U.`password`='" );
        sql.append( user.getPassword() );
        sql.append( "', U.`isactive`='" );
        sql.append( user.getIsActive() );
        sql.append( "', U.`idlocal`='" );
        sql.append( user.getIdLocal() );
        sql.append( "', U.`timezone`=" );

        String formattedTimeZone = user.formatTimeZone();

        if( formattedTimeZone == null ) {
            sql.append( "null" );
            }
        else {
            sql.append( "'" );
            sql.append( formattedTimeZone );
            sql.append( "'" );
            }
        sql.append( " WHERE U.`iduser`='" );
        sql.append( user.getIdUser() );
        sql.append( "';" );

        if( logger.isDebugEnabled() ) {
            logger.debug( "updateUser = " + sql );
            }

        Connection connection = MySQLDAOFactory.getConnection();
        final ConnectionQuery query = new ConnectionQuery( connection );

        try {
            int x = query.doUpdate( sql.toString() );

            if( x == 1 ) {
                return true;
                }
            }
        finally {
            query.quietClose();
            MySQLDAOFactory.returnConnectionToPool( connection );
            }

        return false;
    }

    /**
     *
     * @param sql
     * @param username
     * @return
     * @throws SQLException
     * @throws NamingException
     */
    private int private_connectUser(
            final String    sql,
            final String    username
            ) throws SQLException, NamingException
    {
        logger.info( "connectUser: " + sql );

        Connection connection = MySQLDAOFactory.getConnection();
        final ConnectionQuery query = new ConnectionQuery( connection );

        try {
            ResultSet   rSet        = query.executeQuery( sql );
            int         count       = 0;
            int         iduser      = 0;
            String      uname       = null;

            while( rSet.next() ) {
                iduser  = rSet.getInt( "iduser" );
                uname   = rSet.getString( "username" );
                count++;
                }

            if( count == 1 ) {
                // Extra check (security)
                if( username.equals( uname ) && ! username.isEmpty() ) {

                    return iduser;
                    }
                }

            logger.warn(
                "*** connectUser: result count for '" + username
                    + "' : " + count
                    + " - uname: " + uname
                    );
            }
        catch( SQLException e ) {
            logger.error( "*** connectUser: " + sql, e );
            }
        finally {
            query.quietClose();
            MySQLDAOFactory.returnConnectionToPool( connection );
            }

        // An error is occurred !
        return 0;
    }

}
